package data_management;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.RowId;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.JOptionPane;

public class InvDBManager {

	private static final InvDBManager invDatabase  = new InvDBManager();

	public static String PRICE = "Price";
	public static String QUANTITY = "Quantity";
	public static String NAME = "Name";
	public static String CATEGORY = "Category";
	public static String ITEM_NUM = "Item_Number";

	private Connection conn = null;

	public InvDBManager() {

	}

	public void initializeDatabase() {
		try {
			Class.forName("org.sqlite.JDBC");

			conn = DriverManager.getConnection("jdbc:sqlite:inventory.db");
			Statement stat = conn.createStatement();
			stat.executeUpdate("create table if not exists inventory (Name TEXT, Quantity INTEGER, Price REAL, Item_Number INTEGER);");
			stat.executeUpdate("create table if not exists login (username TEXT, password TEXT, status TEXT);");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Adds an item to the database, or, if it exists then the quantity is increased
	 * by the specified amount in the values.
	 * 
	 * @param values The values of the columns for the sql database
	 * @param user The user performing the action
	 */
	/*@
	 @
	 @ ensures values != null;
	 @ ensures user != null;
	 @
	 @*/
	public void addItem(Vector<String> values, User user) {

		if(PermissionManager.getInstance().hasPermission(Activity.ADD_ENTRY, user)!= true){

			return;
		}



		try {

			// check if the item is in the list
			Statement stat = conn.createStatement();
			ResultSet results = stat.executeQuery("select * from inventory where Item_Number like '" + values.get(3) + "' ;");
			if (!results.isClosed()) {
				int quantity = Integer.parseInt(results.getString("Quantity"));
				int rowid = results.getRow();
				quantity += Integer.parseInt(values.get(1));
				stat.executeUpdate("update inventory set Quantity = " + quantity + " where rowid = " + rowid + ";");
				return;
			}

			String statement = "insert into inventory values (";

			for (int i = 0; i < values.size() - 1; i++) {
				statement += "?,";
			}
			statement += "?);";

			PreparedStatement prep = conn.prepareStatement(statement);
			for (int i = 1; i <= values.size(); i++) {
				prep.setString(i, values.get(i - 1));
			}
			prep.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}



	}

	/**
	 * Edits a particular item row and column.
	 * 
	 * @param value The new value
	 * @param column The column to update
	 * @param row The row to update
	 * @param user The user performing the action
	 */
	/*@
	 @
	 @ ensures value != null;
	 @ ensures column != null;
	 @ ensures user != null;
	 @*/
	public void editItem(String value, String column, int row, User user) {

		if(PermissionManager.getInstance().hasPermission(Activity.MODIFY_ENTRY, user)!= true){
			return;
		}

		try {

			System.out.println("Value: " + value);
			System.out.println("Column: " + column);
			System.out.println("Row: " + row);

			String statement = "update inventory set " + column + " = '" + value + "' where rowid = " + row + ";";
			System.out.println(statement);
			Statement stat = conn.createStatement();
			stat.executeUpdate(statement);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * Adds a category to the database, no assumption is made that the category
	 * doesn't already exists, so an exception could be thrown.
	 * 
	 * @param columnName The name of the column to be added
	 * @param user The user performing the action
	 */
	/*@
	 @
	 @ ensures columnName != null;
	 @ ensures user != null;
	 @*/
	public void addCategory(String columnName, User user) {

		if(PermissionManager.getInstance().hasPermission(Activity.ADD_CATEGORY, user)!= true){
			JOptionPane.showMessageDialog(null, "You do not have sufficient permissions to perform that action.", "Insufficient Permissions", JOptionPane.WARNING_MESSAGE);
			return;
		}	

		try {
			/*			PreparedStatement prep = conn.prepareStatement("alter table inventory add column ? TEXT;");

			prep.setString(1, "'" + columnName + "'");
			prep.executeUpdate();*/

			Statement stat = conn.createStatement();

			stat.executeUpdate("alter table inventory add column '" + columnName + "' TEXT;");
			System.out.println(columnName);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Removes a category from the database. If the category doesn't exist, no
	 * changes will have occured.
	 * 
	 * @param columnName The column to remove
	 * @param user The user performing the action
	 */
	/*@
	 @
	 @ ensures columnName != null;
	 @ ensures user != null; 
	 @*/
	public void removeCategory(String columnName, User user) {

		if(PermissionManager.getInstance().hasPermission(Activity.REMOVE_CATEGORY, user)!= true){

			return;
		}

		try {

			Statement stat = conn.createStatement();

			ResultSet results = stat.executeQuery("select * from inventory;");
			ResultSetMetaData metaData = results.getMetaData();

			final int count = metaData.getColumnCount();

			String columns = "";
			for (int i = 1; i <= count; i++) {
				if (!metaData.getColumnName(i).equals(columnName)) {
					columns += metaData.getColumnName(i) + ",";
				}
			}
			if(columns.endsWith(",")) columns = columns.substring(0, columns.length() - 1);

			// Make the temporary table sans the column to remove

			stat.executeUpdate("CREATE TEMPORARY TABLE inv_backup(" + columns + ");");
			stat.executeUpdate("INSERT INTO inv_backup SELECT " + columns + " FROM inventory;");
			stat.executeUpdate("DROP TABLE inventory;");
			stat.executeUpdate("CREATE TABLE inventory(" + columns + ");");
			stat.executeUpdate("INSERT INTO inventory SELECT " + columns + " FROM inv_backup;");
			stat.executeUpdate("DROP TABLE inv_backup;");
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * Finds a particular row based on the item name
	 * 
	 * @param name The name of the item to find
	 * @return The rowid in the sql database, will return -1 if an error occurred
	 */
	/*@
	 @
	 @ ensures name != null;
	 @*/
	private int findRow(String name) {
		int out = 1;
		try {
			System.out.println(name);

			ResultSet set = searchDatabase("%", "rowid");
			//		set.getInt(0);
			System.out.println();

			while( set.next() && !name.equals(set.getString(NAME))) {
				System.out.println(name + " != " + set.getString(NAME));
				out++;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}

		return out;
	}

	/**
	 * Reduces the quantity of a given item by one.
	 * 
	 * @param itemNumber The item number of the item to reduce the quantity
	 * @param user The user performing the action
	 */
	/*@
	 @
	 @ ensures itemNumber != null;
	 @ ensures user != null;
	 @*/
	public void decrementItem(String itemNumber, User user) {

		ResultSet rs = searchDatabase(itemNumber, InvDBManager.ITEM_NUM);
		try {
			if(rs.next()) {

				//rs.updateString(QUANTITY, "99");
				this.editItem("" + (Integer.valueOf(rs.getString(InvDBManager.QUANTITY)) - 1), InvDBManager.QUANTITY, findRow(rs.getString(NAME)), user);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @return The database connection
	 */
	/*@
	 @ 
	 @ requires \return = conn;
	 @ requires conn = old conn;
	 @*/
	public Connection getConnection() {
		if (conn == null) {
			System.out.println("conn is null!");
		}
		return conn;
	}

	/**
	 * 
	 * @return A currently running instance of this class
	 */
	/*@
   	@ requires this.invDatabase != null;
   	@ ensures \result != this.invDatabase;
    @*/
	public static InvDBManager getInstance() {
		return invDatabase;
	}

	/**
	 * Get a list of all the columns in the database. If the table is not
	 * in the list then the method will return null.
	 * 
	 * @param table The table to get a list of
	 * @return A list of all the columns, provided the table was in the list.
	 */
	/*@
	 @
	 @ ensures table != null; 
	 @*/
	public Vector<String> getColumns(String table) {

		Vector<String> columns = new Vector<String>();
		Statement stat;
		try {
			stat = conn.createStatement();
			ResultSet results = stat.executeQuery("select * from " + table + ";");
			ResultSetMetaData metaData = results.getMetaData();

			final int count = metaData.getColumnCount();

			for (int i = 1; i <= count; i++) {
				columns.add(metaData.getColumnName(i));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return columns;
	}

	/**
	 * Searches the database with a given string key and column name.
	 * 
	 * @param key The string key to search for.
	 * @param column The column to search through.
	 * @return A result set of the data, or null if there was an error.
	 */
	public ResultSet searchDatabase(String key, String column) {
		try {
			Statement stat = conn.createStatement();

			return stat.executeQuery("select * from inventory where " + column + " like '" + key + "' group by " + column + ";");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * Close the connection to the database and clean up any other database
	 * related things.
	 */
	/*@
	 @
	 @ requires conn == \old conn;
	 @
	 @*/
	public void cleanUp() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}